package com.miyu.plugins.baseDao;

import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.setting.dialect.Props;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.blinkfox.fenix.jpa.FenixJpaRepository;
import com.blinkfox.fenix.jpa.FenixJpaRepositoryFactoryBean;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import jakarta.persistence.TypedQuery;
import org.hibernate.query.sql.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 * 数据库操作入口
 * JpaRepository和使用entitymanager 结合使用封装 两种可以同时使用 对应不同人数据使用不同的类型
 * @author miyu
 */
@NoRepositoryBean
public interface BaseDao<T,ID extends Serializable> extends FenixJpaRepository<T,ID>, JpaSpecificationExecutor<T> {



    @Transactional(rollbackFor = Exception.class)
    default void doExecuteBatch(List<String> sqlList) {
        Props props = new Props("application.properties");
        String dburl = props.getProperty("spring.datasource.url");
        String user = props.getProperty("spring.datasource.username");
        String password = props.getProperty("spring.datasource.password");
        String driver = props.getProperty("spring.datasource.driverClassName");
        Connection conn=null;
        Statement stmt=null;
        //加载驱动类
        try {
            Class.forName(driver);
            // 2：创建数据库连接
            conn = DriverManager.getConnection(dburl, user, password);
            conn.setAutoCommit(false);
            int size = sqlList.size();
            // 3：创建执行SQL的对象
            stmt = conn.createStatement();
            for (int i = 0; i < size; i++) {
                stmt.addBatch(sqlList.get(i));
                if(i%1000==0||i==size-1){
                    stmt.executeBatch();
                    stmt.clearBatch();
                    conn.commit();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }finally {
            if (stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn !=null){
                try {
                    conn.close();
                    sqlList.clear();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }

    @Transactional(rollbackFor = Exception.class)
    default void doExecute(String sqlStr){
        Props props = new Props("application.properties");
        String dburl = props.getProperty("spring.datasource.url");
        String user = props.getProperty("spring.datasource.username");
        String password = props.getProperty("spring.datasource.password");
        String driver = props.getProperty("spring.datasource.driverClassName");
        Connection conn=null;
        Statement stmt=null;
        //加载驱动类
        try {
            Class.forName(driver);
            // 2：创建数据库连接
            conn = DriverManager.getConnection(dburl, user, password);
            conn.setAutoCommit(false);
            // 3：创建执行SQL的对象
            stmt = conn.createStatement();
            stmt.execute(sqlStr);
            conn.commit();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }finally {
            if (stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn !=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }

    /**
     * 通过 SQL 查询 JSONarray
     *
     * @param sql SQL
     * @param obj 对象
     * @return {@link JSONArray }
     */
    default JSONArray queryJSONArrayBySql(String sql, Object... obj){
        EntityManager em = SpringUtil.getBean(EntityManager.class);
        Query query = em.createNativeQuery(sql);
        if(obj != null && obj.length > 0)
        {
            for(int i = 0; i < obj.length; i++) {
                query.setParameter(i, obj[i]);
            }
        }
        Query nativeQuery=em.createNativeQuery(sql);
        nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String,Object>> resultList=nativeQuery.getResultList();
        return caseHumpByList(resultList);
    }

    /**
     * 通过 SQL 查询 JSONObject
     *
     * @param sql SQL
     * @param obj 对象
     * @return {@link JSONObject }
     */
    default JSONObject queryJSONObjectBySql(String sql, Object... obj){
        EntityManager em = SpringUtil.getBean(EntityManager.class);
        Query query = em.createNativeQuery(sql);
        if(obj != null && obj.length > 0)
        {
            for(int i = 0; i < obj.length; i++) {
                query.setParameter(i, obj[i]);
            }
        }
        Query nativeQuery=em.createNativeQuery(sql);
        List list = nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
        JSONObject jo = new JSONObject();
        if(list.size()>0) {
            jo = JSONObject.parseObject(JSON.toJSONString(list.get(0))) ;
        }
        return  caseHumpByJSONObject(jo);
    }


    /**
     * 按列表划分案例驼峰
     *
     * @param resultList 结果列表
     * @return {@link JSONArray }
     */
    static JSONArray caseHumpByList(List<Map<String, Object>> resultList) {
        JSONArray rs = new JSONArray();
        if(!resultList.isEmpty()){
            for (int i = 0; i < resultList.size(); i++) {
                Map<String,Object> p  = resultList.get(i);
                JSONObject ps = new JSONObject();
                for (Map.Entry entry : p.entrySet()) {
                    ps.put(caseHump(entry.getKey().toString()),entry.getValue());
                }
                rs.add(ps);
            }
        }


        return rs;
    }

    /**
     * JSONObject  Case Hump
     *
     * @param jsonObject JSON 对象
     * @return {@link JSONObject }
     */
    default JSONObject caseHumpByJSONObject(JSONObject jsonObject){
        JSONObject rs = new JSONObject();
        if(!jsonObject.isEmpty()){
            for (Map.Entry entry : jsonObject.entrySet()) {
                rs.put(caseHump(entry.getKey().toString()),entry.getValue());
            }
        }
        jsonObject.clear();
        return  rs;
    }

    /**
     * 案例驼峰
     *
     * @param str str
     * @return {@link String }
     */
     static String caseHump(String str){
        String upCase;
        char[] buffer = str.toCharArray();
        char a = '_';
        for(int i=0;i<buffer.length;i++){
            char ch = buffer[i];
            if(ch==a){
                char aa= buffer[i+1];
                buffer[i+1] = Character.toUpperCase(aa);
            }
        }
        upCase = (new String(buffer)).replaceAll("_", "");
        return upCase;

    }
}
